package com.sztz.utils;

import cn.hutool.core.date.DateUnit;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcelFactory;
import com.sztz.constants.CommonConstants;
import com.sztz.entity.*;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Slf4j
public class CustomerAmountExcleUtils {
    /**
     * 读取科技的客户EXCEL文件，获取客户信息
     * @return List<InitFirm> 列表数据
     * @author yzy
     * @date 2022/04/07 11:20
     */
    public static List<FirmCustomerInfo> readInitFirmExcel(){
        return EasyExcelFactory.read(new File(CommonConstants.BASE_EXCEL_PATH+"\\statis\\customer.xlsx")).head(FirmCustomerInfo.class).sheet().doReadSync();
    }
    /**
     * 读取科技的客户EXCEL文件，获取客户信息
     * @return List<InitFirm> 列表数据
     * @author yzy
     * @date 2022/04/07 11:20
     */
    public static List<SalesMan> readSalesExcel(){
        return EasyExcelFactory.read(new File(CommonConstants.BASE_EXCEL_PATH+"\\statis\\salesman.csv")).head(SalesMan.class).sheet().doReadSync();
    }

    /**
     * 读取统计EXCEL文件
     * @return List<FirmInfo> 列表数据
     * @author yzy
     * @date 2022/04/07 11:20
     */
    public static List<CustomerAmount> readSalesManExcel(String path){
        return EasyExcelFactory.read(new File(CommonConstants.BASE_EXCEL_PATH+path)).head(CustomerAmount.class).sheet().doReadSync();
    }

    public static void main(String[] args) {
        //monthSum();
        //totalSum();
    }
    public static void totalSum(){
        List<FirmCustomerInfo> customer = readInitFirmExcel();

        List<CustomerAmount> landmfAmounts = new ArrayList<>();
        List<CustomerAmount> bundleAmounts = new ArrayList<>();
        List<CustomerAmount> tdecAmounts = new ArrayList<>();
        List<CustomerAmount> oceanAmounts = new ArrayList<>();
        List<CustomerAmount> gzOceanAmounts = new ArrayList<>();
        List<CustomerAmount> oceanDaiAmounts = new ArrayList<>();
        List<CustomerAmount> rocarsAmounts = new ArrayList<>();
        List<CustomerAmount> hkBundleAmounts = new ArrayList<>();
        List<CustomerAmount> hkTdecAmounts = new ArrayList<>();
        List<CustomerAmount> hkTdecDaiAmounts = new ArrayList<>();
        List<CustomerAmount> cebAmounts = new ArrayList<>();
        List<CustomerAmount> goldAmounts = new ArrayList<>();
        List<CustomerAmount> weiHuAmounts = new ArrayList<>();
        for(int i=1;i<13;i++){

            String landmf= StrUtil.format("\\statis{}\\landmf.xlsx","\\"+i);
            List<CustomerAmount> landmfMonth = readSalesManExcel(landmf);
            landmfAmounts.addAll(landmfMonth);

            String bundle= StrUtil.format("\\statis{}\\bundle.xlsx","\\"+i);
            List<CustomerAmount> bundleMont = readSalesManExcel(bundle);
            bundleAmounts.addAll(bundleMont);

            String tdec= StrUtil.format("\\statis{}\\tdec.xlsx","\\"+i);
            List<CustomerAmount> tdecMonth = readSalesManExcel(tdec);
            tdecAmounts.addAll(tdecMonth);

            String ocean= StrUtil.format("\\statis{}\\ocean.xlsx","\\"+i);
            List<CustomerAmount> oceanMonth = readSalesManExcel(ocean);
            oceanAmounts.addAll(oceanMonth);

            String gzOcean= StrUtil.format("\\statis{}\\gz_ocean.xlsx","\\"+i);
            List<CustomerAmount> gzOceanMonth = readSalesManExcel(gzOcean);
            gzOceanAmounts.addAll(gzOceanMonth);

            String oceanDai= StrUtil.format("\\statis{}\\ocean_dai.xlsx","\\"+i);
            List<CustomerAmount> oceanDaiMonth = readSalesManExcel(oceanDai);
            oceanDaiAmounts.addAll(oceanDaiMonth);

            String rocars= StrUtil.format("\\statis{}\\rocars.xlsx","\\"+i);
            List<CustomerAmount> rocarsMonth = readSalesManExcel(rocars);
            rocarsAmounts.addAll(rocarsMonth);

            String hkBundle= StrUtil.format("\\statis{}\\hk_bundle.xlsx","\\"+i);
            List<CustomerAmount> hkBundleMonth = readSalesManExcel(hkBundle);
            hkBundleAmounts.addAll(hkBundleMonth);

            String hkTdec= StrUtil.format("\\statis{}\\hk_tdec.xlsx","\\"+i);
            List<CustomerAmount> hkTdecMonth = readSalesManExcel(hkTdec);
            hkTdecAmounts.addAll(hkTdecMonth);

            String hkTdecDai= StrUtil.format("\\statis{}\\hk_tdec_dai.xlsx","\\"+i);
            List<CustomerAmount> hkTdecDaiMonth = readSalesManExcel(hkTdecDai);
            hkTdecDaiAmounts.addAll(hkTdecDaiMonth);

            String ceb= StrUtil.format("\\statis{}\\ceb.xlsx","\\"+i);
            List<CustomerAmount> cebMonth = readSalesManExcel(ceb);
            cebAmounts.addAll(cebMonth);

            String gold= StrUtil.format("\\statis{}\\gold.xlsx","\\"+i);
            List<CustomerAmount> goldMonth = readSalesManExcel(gold);
            goldAmounts.addAll(goldMonth);

            String weiHu= StrUtil.format("\\statis{}\\wei_hu.xlsx","\\"+i);
            List<CustomerAmount> weiHuMonth = readSalesManExcel(weiHu);
            weiHuAmounts.addAll(weiHuMonth);
        }
        Map<String, Double> landmfAmt = landmfAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
        Map<String, Double> bundleAmt = bundleAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
        Map<String, Double> tdecAmt = tdecAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
        Map<String, Double> oceanAmt = oceanAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
        Map<String, Double> oceanDaiAmt = oceanDaiAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
        Map<String, Double> gzOceanAmt = gzOceanAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
        Map<String, Double> rocarsAmt = rocarsAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
        Map<String, Double> hkBundleAmt = hkBundleAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
        Map<String, Double> hkTdecAmt = hkTdecAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
        Map<String, Double> hkTdecDaiAmt = hkTdecDaiAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
        Map<String, Double> cebAmt = cebAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
        Map<String, Double> goldAmt = goldAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
        Map<String, Double> weiHuAmt = weiHuAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
        List<SalesMan> salesMEN = readSalesExcel();
        List<CustomerAmtStatistics> collect = customer.stream().map(item -> {
            CustomerAmtStatistics castatis = new CustomerAmtStatistics();
            castatis.setFullName(item.getFullName());
            castatis.setLandmf(landmfAmt.getOrDefault(item.getRegisteredNo(), 0.0));
            castatis.setBundle(bundleAmt.getOrDefault(item.getRegisteredNo(), 0.0));
            castatis.setTdec(tdecAmt.getOrDefault(item.getRegisteredNo(), 0.0));
            castatis.setOcean(oceanAmt.getOrDefault(item.getRegisteredNo(), 0.0));
            castatis.setGzOcean(gzOceanAmt.getOrDefault(item.getRegisteredNo(), 0.0));
            castatis.setOceanDai(oceanDaiAmt.getOrDefault(item.getRegisteredNo(), 0.0));
            castatis.setRocars(rocarsAmt.getOrDefault(item.getRegisteredNo(), 0.0));
            castatis.setHkBundle(hkBundleAmt.getOrDefault(item.getRegisteredNo(), 0.0));
            castatis.setHkTdec(hkTdecAmt.getOrDefault(item.getRegisteredNo(), 0.0));
            castatis.setHkTdecDai(hkTdecDaiAmt.getOrDefault(item.getRegisteredNo(), 0.0));
            castatis.setCeb(cebAmt.getOrDefault(item.getRegisteredNo(), 0.0));
            castatis.setGoldTwo(goldAmt.getOrDefault(item.getRegisteredNo(), 0.0));
            castatis.setWeiHu(weiHuAmt.getOrDefault(item.getRegisteredNo(), 0.0));
            //TODO
            castatis.setSalesMane("");
            return castatis;
        }).collect(Collectors.toList());
        List<CustomerAmtStatistics> filterColl = collect.stream().filter(CustomerAmtStatistics::hanAmt).collect(Collectors.toList());
        String outFilePath= StrUtil.concat(Boolean.TRUE,CommonConstants.BASE_EXCEL_PATH,StrUtil.format("\\statis\\out\\amountTotalYear.xlsx"));
        EasyExcelFactory.write(FileUtil.file(outFilePath), CustomerAmtStatistics.class).sheet().doWrite(filterColl);

    }

    public static void monthSum(){
        for(int i=1;i<13;i++){
            List<FirmCustomerInfo> customer = readInitFirmExcel();

            List<CustomerAmount> landmfAmounts = new ArrayList<>();
            List<CustomerAmount> bundleAmounts = new ArrayList<>();
            List<CustomerAmount> tdecAmounts = new ArrayList<>();
            List<CustomerAmount> oceanAmounts = new ArrayList<>();
            List<CustomerAmount> gzOceanAmounts = new ArrayList<>();
            List<CustomerAmount> oceanDaiAmounts = new ArrayList<>();
            List<CustomerAmount> rocarsAmounts = new ArrayList<>();
            List<CustomerAmount> hkBundleAmounts = new ArrayList<>();
            List<CustomerAmount> hkTdecAmounts = new ArrayList<>();
            List<CustomerAmount> hkTdecDaiAmounts = new ArrayList<>();
            List<CustomerAmount> cebAmounts = new ArrayList<>();
            List<CustomerAmount> goldAmounts = new ArrayList<>();
            List<CustomerAmount> weiHuAmounts = new ArrayList<>();

            String landmf= StrUtil.format("\\statis{}\\landmf.xlsx","\\"+i);
            List<CustomerAmount> landmfMonth = readSalesManExcel(landmf);
            landmfAmounts.addAll(landmfMonth);

            String bundle= StrUtil.format("\\statis{}\\bundle.xlsx","\\"+i);
            List<CustomerAmount> bundleMont = readSalesManExcel(bundle);
            bundleAmounts.addAll(bundleMont);

            String tdec= StrUtil.format("\\statis{}\\tdec.xlsx","\\"+i);
            List<CustomerAmount> tdecMonth = readSalesManExcel(tdec);
            tdecAmounts.addAll(tdecMonth);

            String ocean= StrUtil.format("\\statis{}\\ocean.xlsx","\\"+i);
            List<CustomerAmount> oceanMonth = readSalesManExcel(ocean);
            oceanAmounts.addAll(oceanMonth);

            String gzOcean= StrUtil.format("\\statis{}\\gz_ocean.xlsx","\\"+i);
            List<CustomerAmount> gzOceanMonth = readSalesManExcel(gzOcean);
            gzOceanAmounts.addAll(gzOceanMonth);

            String oceanDai= StrUtil.format("\\statis{}\\ocean_dai.xlsx","\\"+i);
            List<CustomerAmount> oceanDaiMonth = readSalesManExcel(oceanDai);
            oceanDaiAmounts.addAll(oceanDaiMonth);

            String rocars= StrUtil.format("\\statis{}\\rocars.xlsx","\\"+i);
            List<CustomerAmount> rocarsMonth = readSalesManExcel(rocars);
            rocarsAmounts.addAll(rocarsMonth);

            String hkBundle= StrUtil.format("\\statis{}\\hk_bundle.xlsx","\\"+i);
            List<CustomerAmount> hkBundleMonth = readSalesManExcel(hkBundle);
            hkBundleAmounts.addAll(hkBundleMonth);

            String hkTdec= StrUtil.format("\\statis{}\\hk_tdec.xlsx","\\"+i);
            List<CustomerAmount> hkTdecMonth = readSalesManExcel(hkTdec);
            hkTdecAmounts.addAll(hkTdecMonth);

            String hkTdecDai= StrUtil.format("\\statis{}\\hk_tdec_dai.xlsx","\\"+i);
            List<CustomerAmount> hkTdecDaiMonth = readSalesManExcel(hkTdecDai);
            hkTdecDaiAmounts.addAll(hkTdecDaiMonth);

            String ceb= StrUtil.format("\\statis{}\\ceb.xlsx","\\"+i);
            List<CustomerAmount> cebMonth = readSalesManExcel(ceb);
            cebAmounts.addAll(cebMonth);

            String gold= StrUtil.format("\\statis{}\\gold.xlsx","\\"+i);
            List<CustomerAmount> goldMonth = readSalesManExcel(gold);
            goldAmounts.addAll(goldMonth);

            String weiHu= StrUtil.format("\\statis{}\\wei_hu.xlsx","\\"+i);
            List<CustomerAmount> weiHuMonth = readSalesManExcel(weiHu);
            weiHuAmounts.addAll(weiHuMonth);

            Map<String, Double> landmfAmt = landmfAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
            Map<String, Double> bundleAmt = bundleAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
            Map<String, Double> tdecAmt = tdecAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
            Map<String, Double> oceanAmt = oceanAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
            Map<String, Double> oceanDaiAmt = oceanDaiAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
            Map<String, Double> gzOceanAmt = gzOceanAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
            Map<String, Double> rocarsAmt = rocarsAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
            Map<String, Double> hkBundleAmt = hkBundleAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
            Map<String, Double> hkTdecAmt = hkTdecAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
            Map<String, Double> hkTdecDaiAmt = hkTdecDaiAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
            Map<String, Double> cebAmt = cebAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
            Map<String, Double> goldAmt = goldAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
            Map<String, Double> weiHuAmt = weiHuAmounts.stream().collect(Collectors.groupingBy(CustomerAmount::getRegisteredNo, Collectors.summingDouble(CustomerAmount::getAmount)));
            List<CustomerAmtStatistics> collect = customer.stream().map(item -> {
                CustomerAmtStatistics castatis = new CustomerAmtStatistics();
                castatis.setFullName(item.getFullName());
                castatis.setLandmf(landmfAmt.getOrDefault(item.getRegisteredNo(), 0.0));
                castatis.setBundle(bundleAmt.getOrDefault(item.getRegisteredNo(), 0.0));
                castatis.setTdec(tdecAmt.getOrDefault(item.getRegisteredNo(), 0.0));
                castatis.setOcean(oceanAmt.getOrDefault(item.getRegisteredNo(), 0.0));
                castatis.setGzOcean(gzOceanAmt.getOrDefault(item.getRegisteredNo(), 0.0));
                castatis.setOceanDai(oceanDaiAmt.getOrDefault(item.getRegisteredNo(), 0.0));
                castatis.setRocars(rocarsAmt.getOrDefault(item.getRegisteredNo(), 0.0));
                castatis.setHkBundle(hkBundleAmt.getOrDefault(item.getRegisteredNo(), 0.0));
                castatis.setHkTdec(hkTdecAmt.getOrDefault(item.getRegisteredNo(), 0.0));
                castatis.setHkTdecDai(hkTdecDaiAmt.getOrDefault(item.getRegisteredNo(), 0.0));
                castatis.setCeb(cebAmt.getOrDefault(item.getRegisteredNo(), 0.0));
                castatis.setGoldTwo(goldAmt.getOrDefault(item.getRegisteredNo(), 0.0));
                castatis.setWeiHu(weiHuAmt.getOrDefault(item.getRegisteredNo(), 0.0));
                return castatis;
            }).collect(Collectors.toList());
            String outFilePath= StrUtil.concat(Boolean.TRUE,CommonConstants.BASE_EXCEL_PATH,StrUtil.format("\\statis\\out\\amount12-{}.xlsx",i));
            EasyExcelFactory.write(FileUtil.file(outFilePath), CustomerAmtStatistics.class).sheet().doWrite(collect);
        }

    }
}
